# -*- coding: utf-8 -*-
# @Time: 2024/9/13 11:28
# @Author: foxhuty
# @File: datasource.py
# @Software: PyCharm
# @Based on python 3.10
import warnings

import pandas as pd
from sympy import symbols, solve

from pandas.core.common import SettingWithCopyWarning

# pd.options.mode.copy_on_write = False
warnings.simplefilter(action="ignore", category=SettingWithCopyWarning)


class DataSource:
    """
    因为性能原因，该类已弃用。
    """
    # 各个等级赋分区间（T1——T2）
    A_T_range = [86, 100]
    B_T_range = [71, 85]
    C_T_range = [56, 70]
    D_T_range = [41, 55]
    E_T_range = [30, 40]

    def __init__(self, file):
        self.file = file

    def get_data(self):
        """

        :return: 数据列表（物理类，历史类或不分类的原始excel数据表
        """
        excel_file = pd.ExcelFile(self.file)
        # print(excel_file.sheet_names)
        excel_file = pd.ExcelFile(self.file)
        return [pd.read_excel(excel_file, sheet_name) for sheet_name in excel_file.sheet_names]

    def get_grade_data(self):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值后生成excel数据表
        :return: None
        """
        data = self.get_data()[1]
        # df_history = self.get_data()[1]
        # add_subjects_list_physics = [i for i in df_physics.columns if i in ['政治', '地理', '生物', '化学']]
        # add_subjects_list_history = [i for i in df_history.columns if i in ['政治', '地理', '生物', '化学']]
        # add_subjects_list = [i for i in data.columns if i in ['政治', '地理', '生物', '化学']]
        subjects = [col for col in data.columns if col in ['政治', '地理', '生物', '化学']]
        for subject in subjects:
            data = self.get_grade(data, subject)
        data.to_excel(self.file.split('.')[0] + '等级赋分表.xlsx', index=False)
        print('successfully done')

    def get_grade(self, data, subject):
        """
        分别获取政治，地理，化学和生物学科的赋分等级和赋分值
        :param data: df数据表
        :param subject: 学科
        :return: 返回获取了赋分等级和赋分值的df数据
        """
        # 获取各等级的卷面分区间（Y1-Y2)
        max_score, min_score = self.get_subject_max_min_score(data, subject)
        # data[subject + '等级'] = None
        data.loc[:, subject + '等级'] = None
        # data[subject + '赋值'] = None
        data.loc[:, subject + '赋值'] = None

        for i in data.index:
            if data[subject][i] >= min_score[0]:
                data[subject + '等级'][i] = 'A'
                data[subject + '赋值'][i] = self.get_added_score(data[subject][i], min_score[0], max_score[0],
                                                                 DataSource.A_T_range[0], DataSource.A_T_range[1])
            elif data[subject][i] >= min_score[1]:
                data[subject + '等级'][i] = 'B'
                data[subject + '赋值'][i] = self.get_added_score(data[subject][i], min_score[1], max_score[1],
                                                                 DataSource.B_T_range[0], DataSource.B_T_range[1])
            elif data[subject][i] >= min_score[2]:
                data[subject + '等级'][i] = 'C'
                data[subject + '赋值'][i] = self.get_added_score(data[subject][i], min_score[2], max_score[2],
                                                                 DataSource.C_T_range[0], DataSource.C_T_range[1])
            elif data[subject][i] >= min_score[3]:
                data[subject + '等级'][i] = 'D'
                data[subject + '赋值'][i] = self.get_added_score(data[subject][i], min_score[3], max_score[3],
                                                                 DataSource.D_T_range[0], DataSource.D_T_range[1])
            elif data[subject][i] < min_score[3]:
                data[subject + '等级'][i] = 'E'
                data[subject + '赋值'][i] = self.get_added_score(data[subject][i], min_score[4], max_score[4],
                                                                 DataSource.E_T_range[0], DataSource.E_T_range[1])

        return data

    @staticmethod
    def get_subject_max_min_score(data, subject):
        """
        用于计算获取各等级的卷面分区间（Y1-Y2)
        :param data:
        :param subject:
        :return: 各等级的卷面分区间（Y1-Y2)
        """
        max_score = []
        min_score = []
        subject_num = data[subject].count()
        A_num = int(subject_num * 0.15)
        B_num = int(subject_num * 0.35)
        C_num = int(subject_num * 0.35)
        D_num = int(subject_num * 0.13)
        data.sort_values(by=subject, ascending=False, inplace=True, ignore_index=True)
        data_subject_A = data.loc[:A_num - 1, subject]

        A_max = data_subject_A.max()
        A_min = data_subject_A.min()
        max_score.append(A_max)
        min_score.append(A_min)
        final_data_subject_A = data[data[subject] >= A_min]
        data_subject_B = data.loc[len(final_data_subject_A):(len(final_data_subject_A) + B_num - 1), subject]

        B_max = data_subject_B.max()
        B_min = data_subject_B.min()
        max_score.append(B_max)
        min_score.append(B_min)

        final_data_subject_B = data[(data[subject] >= B_min) & (data[subject] <= B_max)]

        data_subject_C = data.loc[(len(final_data_subject_A) + len(final_data_subject_B)):(
                len(final_data_subject_A) + len(final_data_subject_B) + C_num - 1), subject]

        C_max = data_subject_C.max()
        C_min = data_subject_C.min()
        max_score.append(C_max)
        min_score.append(C_min)

        final_data_subject_C = data[(data[subject] >= C_min) & (data[subject] <= C_max)]

        data_subject_D = data.loc[(len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C)):(
                len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + D_num - 1), subject]
        d_max = data_subject_D.max()
        d_min = data_subject_D.min()
        max_score.append(d_max)
        min_score.append(d_min)

        final_data_subject_D = data[(data[subject] >= d_min) & (data[subject] <= d_max)]

        final_data_subject_E = data.loc[
                               (len(final_data_subject_A) + len(final_data_subject_B) + len(final_data_subject_C) + len(
                                   final_data_subject_D)):subject_num - 1, subject]
        E_max = final_data_subject_E.max()
        E_min = final_data_subject_E.min()
        max_score.append(E_max)
        min_score.append(E_min)

        print(f'{subject}学科等级卷面Y1的值分别为：{min_score}, Y2的值分别为：{max_score}')
        print(
            f'{subject}学科A等级人数为：{len(final_data_subject_A)},'
            f'B等级人数为：{len(final_data_subject_B)},'
            f'C等级人数为：{len(final_data_subject_C)},'
            f'D等级人数为：{len(final_data_subject_D)},'
            f'E等级人数为：{len(final_data_subject_E)}')
        return max_score, min_score

    @staticmethod
    def get_added_score(y, y1, y2, t1, t2):
        """
        计算赋分值的公式：高考赋分方法，其中y为原始卷面得分，t为赋值得分，
        t1和t2为所在等级赋值区间的下限和上限，y1和y2为卷面所在等级分数区间的下限和上限。
        :param y:
        :param y1:
        :param y2:
        :param t1:
        :param t2:
        :return: 一个学生的赋值得分（四舍五入取整）
        """
        t = symbols('t')
        # 根据赋分公式，当y=y1时，t=t1
        if y == y1:
            scores_added = t1
            return scores_added
        else:
            scores_added = solve((t2 - t) / (t - t1) - (y2 - y) / (y - y1), t)
            return round(scores_added[0])


if __name__ == '__main__':
    # file_path = r'D:\data_test\高2022级零诊成绩测试数据.xlsx'
    file_path = r'D:\data_test\高2026级学生10月考成绩汇总.xlsx'
    datasource = DataSource(file_path)
    datasource.get_grade_data()
